#!/usr/bin/perl
#

use strict;
use Getopt::Long;
use SqlCI;

my ($table, $part) = (0,1);

GetOptions("table" => sub { $table=1; $part=0; },
          "part"  => sub { $table=0; $part=1; },
         );

my $sqlci = SqlCI->new();

my @catalogs = map { (split /\s+/)[0] } grep {/ _CATALOG/} $sqlci->execute(
      "SELECT trim(cat_name) || ' _CATALOG'",
      "FROM hp_system_catalog.system_schema.catsys",
      "FOR read uncommitted access;");

for my $cat (@catalogs) {

    print "$cat: ";
    if ($table) {
        show_table($cat, $sqlci);
    }
    else {
        show_partition($cat, $sqlci);
    }
}

$sqlci->close();

exit 0;


######## subroutines #########

sub trim {
    my $str = shift;
    $str =~ s/^\s+//;
    $str =~ s/\s+$//;

    $str;
}

sub show_table {
    my ($cat, $sqlci) = @_;

    my @tables = grep { !/\@/}              # get rid of tables with @
                   map { (split /\s+/)[0] } # get the first item
                     grep { / _TABLE / }    # contains _TABLE
                         $sqlci->execute(
        "SELECT trim(s.schema_name) || '.' || trim(o.object_name)"
                . " || ' _TABLE'",
        "FROM $cat.hp_definition_schema.objects o,",
        "      hp_system_catalog.system_schema.schemata s",
        "WHERE o.schema_uid = s.schema_uid",
        "      and o.object_type = 'BT'",
        "      and o.object_name_space = 'TA'",
#       "      and o.object_security_class = 'UT'",
        "ORDER BY s.schema_name, o.object_name",
        "FOR read uncommitted access;");

    print scalar @tables, "\n";

    print "    $cat.$_\n" for (@tables);
}

sub show_partition {
    my ($cat, $sqlci) = @_;

    my @files = map { trim($_) } grep { / => / } $sqlci->execute(
        "SELECT trim(s.schema_name) || '.' || trim(o.object_name) || ' => ' || trim(p.data_source) || '.' || trim(p.file_suffix)",
        "FROM $cat.hp_definition_schema.objects o,",
        "     $cat.hp_definition_schema.partitions p,",
        "     hp_system_catalog.system_schema.schemata s",
        "WHERE o.object_uid = p.object_uid",
        "      and o.schema_uid = s.schema_uid",
        "      and o.object_type = 'BT'",
        "      and o.object_name_space = 'TA'",
#       "      and o.object_security_class = 'UT'",
        "ORDER BY s.schema_name, o.object_name, p.data_source",
        "FOR read uncommitted access;");

     print scalar @files, "\n";
     print "    $cat.$_\n" for (@files);
}
 
